package com.example.dataextraction.utils;

import com.alibaba.fastjson.JSONObject;
import com.example.dataextraction.enums.DBType;
import com.example.dataextraction.model.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.Reader;
import java.sql.*;
import java.util.*;

public class DBHelper {

    private  Logger LOG = LoggerFactory.getLogger("DBHelper");

    // 定义变量
    private  Connection conn = null;
    // 大多数情况下用preparedstatement替代statement
    private  PreparedStatement ps = null;
    private  Statement statement;
    private  ResultSet rs = null;

    // 连接数据库的参数
    private  String url = "";
    private  String username = "";
    private  String driver = "";
    private  String pass = "";

    private  DataSource db;

    public DBHelper(DataSource database) throws Exception {
        init(database);
    }

    public  void init(DataSource database) throws Exception {
        db = database;
        try {
            driver = database.getDriver();
            url = database.getUrl();
            username = database.getUsername();

            pass = Encryp3DES.discryptDes(database.getPassword());
            Class.forName(driver);// 指定连接类型
        } catch (Exception e) {
            LOG.error("", e);
            throw e;
        }
    }

    public Connection getConnection() throws Exception {
        try {
            conn = DriverManager.getConnection(url, username, pass);
        } catch (Exception e) {
            LOG.error(e.getMessage());
            throw e;
        }
        return conn;
    }


    /**
     * 执行数据库查询操作
     *
     * @param sql
     * @param parameters
     * @return
     * @throws Exception
     */
    public ResultSet executeQuery(String sql, List<Object> parameters) throws Exception {
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            if (parameters != null && parameters.size() > 0) {
                for (int i = 0; i < parameters.size(); i++) {
                    ps.setObject(i + 1, parameters.get(i));
                }
            }
            rs = ps.executeQuery();
        } catch (Exception e) {
            LOG.error(e.getMessage());
            throw e;
        }
        return rs;
    }

    public boolean executeQueryBoolean(String sql, List<Object> parameters) throws Exception {
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            if (parameters != null && parameters.size() > 0) {
                for (int i = 0; i < parameters.size(); i++) {
                    ps.setObject(i + 1, parameters.get(i));
                }
            }
            ps.executeUpdate();
            return true;
        } catch (Exception e) {
            LOG.error(e.getMessage());
            //throw e;
            return false;
        }

    }


    public ResultSet getResultSetInfoPrepareStatement(String sql, int num) throws Exception {
        ResultSet rs = null;
        try {
            conn = getConnection();
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
            ps.setFetchSize(num);
            rs = ps.executeQuery(sql);
            return rs;
        } catch (Exception e) {
            //e.printStackTrace();
            LOG.error(e.getMessage());
            throw e;
        } finally {
            //conn.close();
            //conn.setAutoCommit(true);
        }
    }

    /**
     * 执行hive查询
     *
     * @param sql
     * @return
     * @throws Exception
     */
    public  ResultSet executeHiveQuery(String sql) throws Exception {
        try {
            conn = getConnection();
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
        } catch (Exception e) {
            LOG.error(e.getMessage());
            throw e;
        }
        return rs;
    }

    /**
     * 根据表名获取数据库表信息
     *
     * @param tableName
     * @return
     * @throws Exception
     */
    public  ResultSet queryTableInfo(String tableName) throws Exception {
        try {
            conn = getConnection();
            DatabaseMetaData md = conn.getMetaData();
            rs = md.getTables(null, null, tableName, null);
        } catch (Exception e) {
            LOG.error(e.getMessage());
            throw e;
        }
        return rs;
    }

    /**
     * 根据表名、字段名获取数据库表字段信息
     *
     * @param tableName
     * @param columnName
     * @return
     * @throws Exception
     */
    public  ResultSet queryTableColumnInfo(String tableName, String columnName) throws Exception {
        try {
            conn = getConnection();
            DatabaseMetaData md = conn.getMetaData();
            rs = md.getColumns(null, null, tableName, columnName);
        } catch (Exception e) {
            LOG.error(e.getMessage());
            throw e;
        }
        return rs;
    }

    /**
     * 公共执行jdbc类(commit)
     *
     * @param dbType
     * @param sql
     * @return
     * @throws Exception
     */
    public boolean getExcuterResult(DBType dbType, String sql) throws Exception {
        boolean flag = true;
        try {
            switch (dbType) {
                case MYSQL:
                    flag = executeSql(sql, true);
                    break;
                case ORACLE:
                    flag = executeSql(sql, true);
                    break;
                case HIVE:
                    flag = executeHiveSql(sql, true);
                    break;
                case SQLSERVER:
                    flag = executeHiveSql(sql, true);
                    break;
                default:
                    flag = executeSql(sql, true);
            }
        } catch (Exception e) {
            LOG.error(e.getMessage());
            throw e;
        }
        return flag;

    }


    /**
     * 判断Mysql表是否存在
     *
     * @param tableName
     * @return
     * @throws Exception
     */
    public boolean tableExist(String tableName) throws Exception {
        boolean b = false;
        try {
            conn = getConnection();
            DatabaseMetaData md = conn.getMetaData();
            rs = md.getTables(db.getServiceName(), null, tableName, new String[]{"TABLE"});
            b = rs.next();
        } catch (Exception e) {
            LOG.error(e.getMessage());
            throw e;
        } finally {
            close();
        }
        return b;
    }

    /**
     * 判断oracle表是否存在
     *
     * @param tableName
     * @return
     * @throws Exception
     */
    public  boolean oracleTableExist(String tableName) throws Exception {
        boolean b = false;
        try {
            conn = getConnection();
            DatabaseMetaData md = conn.getMetaData();
            rs = md.getTables(null, db.getUsername().toUpperCase(), tableName, new String[]{"TABLE"});
            b = rs.next();
        } catch (Exception e) {
            LOG.error(e.getMessage());
            throw e;
        } finally {
            close();
        }
        return b;
    }

    /**
     * 获取表信息
     *
     * @param sql
     * @return
     * @throws Exception
     */
    public  List<Map<String, Object>> getTableInfo(String sql) throws Exception {
        List<Map<String, Object>> list = new ArrayList<>();
        try {
            conn = getConnection();
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            while (rs.next()) {
                Map<String, Object> map = new HashMap<>();
                map.put("tableName", rs.getObject(1));
                map.put("tableComment", rs.getString(2));
                list.add(map);
            }
        } catch (Exception e) {
            LOG.error(e.getMessage());
            throw e;
        } finally {
            close();
        }
        return list;
    }

    /**
     * 获取某信息的集合
     *
     * @param sql
     * @return
     * @throws Exception
     */
    public  Map<String, Object> getCommonInfo(String sql) throws Exception {
        Map<String, Object> map = new HashMap<>();
        try {
            conn = getConnection();
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            while (rs.next()) {
                map.put(rs.getObject(1).toString(), rs.getObject(1));
            }
        } catch (Exception e) {
            LOG.error(e.getMessage());
            throw e;
        } finally {
            close();
        }
        return map;
    }

    /**
     * 获取表字段信息
     *
     * @param sql
     * @return
     * @throws Exception
     */
    public  List<Map<String, Object>> getColumnInfo(String sql) throws Exception {
        List<Map<String, Object>> mapList = new ArrayList<>();
        try {
            conn = getConnection();
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            while (rs.next()) {
                Map<String, Object> map = new LinkedHashMap<>();
                map.put("colName", rs.getObject(1));
                map.put("formName", rs.getObject(2));
                map.put("primaryKey", rs.getObject(3));
//				map.put("colType",rs.getObject(3));
//				map.put("colSize",rs.getObject(4));
//				map.put("primaryKey",rs.getObject(5));
//				map.put("colPointSize",rs.getObject(6));
//				map.put("colEmpty",rs.getObject(7));
                mapList.add(map);
            }
        } catch (Exception e) {
            LOG.error(e.getMessage());
            throw e;
        } finally {
            close();
        }
        return mapList;
    }

    /**
     * 判断hive数据库某种表是否存在
     *
     * @param tableName
     * @return
     * @throws Exception
     */
    public  boolean hiveTableExist(String tableName) throws Exception {
        boolean b = false;
        String sql = "show tables '?'";
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, tableName);
            rs = ps.executeQuery();
            b = rs.next();
        } catch (Exception e) {
            LOG.error(e.getMessage());
            throw e;
        } finally {
            close();
        }
        return b;
    }

    /**
     * 根据表名、字段名判断列名是否存在
     *
     * @param tableName
     * @param columnName
     * @return
     * @throws Exception
     */
    public  boolean columnExist(String tableName, String columnName) throws Exception {
        boolean b = false;
        try {
            conn = getConnection();
            DatabaseMetaData md = conn.getMetaData();
            rs = md.getColumns(db.getServiceName(), null, tableName, columnName);
            b = rs.next();
        } catch (Exception e) {
            LOG.error(e.getMessage());
            throw e;
        } finally {
            close();
        }
        return b;
    }

    /**
     * 判断oracle表字段是否存在
     *
     * @param tableName
     * @param columnName
     * @return
     * @throws Exception
     */
    public  boolean oracleColumnExist(String tableName, String columnName) throws Exception {
        boolean b = false;
        try {
            conn = getConnection();
            DatabaseMetaData md = conn.getMetaData();
            rs = md.getColumns(null, db.getUsername().toUpperCase(), tableName.toUpperCase(), columnName.toUpperCase());
            b = rs.next();
        } catch (Exception e) {
            LOG.error(e.getMessage());
            throw e;
        } finally {
            close();
        }
        return b;
    }

    /**
     * 判断是否存在主键
     *
     * @param tableName
     * @return
     * @throws Exception
     */
    public  boolean keyExist(String tableName) throws Exception {
        boolean b = false;
        try {
            conn = getConnection();
            DatabaseMetaData md = conn.getMetaData();
            rs = md.getPrimaryKeys(null, null, tableName);
            b = rs.next();
        } catch (Exception e) {
            LOG.error(e.getMessage());
            throw e;
        } finally {
            close();
        }
        return b;
    }

    /**
     * 执行sql语句
     *
     * @param sql
     * @throws Exception
     * @returnexecuteQuery
     */
    public boolean executeSql(String sql, Boolean commitFlag) throws Exception {
        boolean b = false;
        try {
            conn = getConnection();
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(sql);
            ps.execute(sql);
            if (commitFlag) {
                conn.commit();
            }
            b = true;
        } catch (SQLException e) {
            LOG.error(e.getMessage());
            throw e;
        } finally {
            close();
        }
        return b;
    }

    /**
     * 执行sql语句
     *
     * @param sql
     * @return
     * @throws Exception
     */
    public  boolean executeImpalaSql(String sql, Boolean commitFlag) throws Exception {
        boolean b = false;
        try {
            conn = getConnection();
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(sql);
            ps.execute();
            if (commitFlag) {
                conn.commit();
            }
            b = true;
        } catch (SQLException e) {
            LOG.error(e.getMessage());
            throw e;
        } finally {
            close();
        }
        return b;
    }

    /**
     * 执行sql语句
     *
     * @param sql
     * @return
     * @throws Exception
     */
    public  boolean executeHiveSql(String sql, Boolean commitFlag) throws Exception {
        boolean b = false;
        try {
            conn = getConnection();
            conn.setAutoCommit(false);
            statement = conn.createStatement();
            statement.execute(sql);
            if (commitFlag) {
                conn.commit();
            }
            b = true;
        } catch (SQLException e) {
            LOG.error(e.getMessage());
            throw e;
        } finally {
            close();
        }
        return b;
    }

    /**
     * 将数据集转换成List
     *
     * @param rs
     * @return
     * @throws SQLException
     */
    public  List<Map<String, Object>> resultSetToList(ResultSet rs) throws SQLException {
        if (rs == null) {
            return null;
        }
        ResultSetMetaData md = rs.getMetaData(); // 得到结果集(rs)的结构信息，比如字段数、字段名等
        int columnCount = md.getColumnCount(); // 返回此 ResultSet 对象中的列数
        List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
        Map<String, Object> rowData = new HashMap<String, Object>();
        while (rs.next()) {
            rowData = new HashMap<String, Object>(columnCount);
            for (int i = 1; i <= columnCount; i++) {
                rowData.put(md.getColumnLabel(i), rs.getObject(i));
            }
            list.add(rowData);
        }
        return list;
    }

    /**
     * 根据结果集获取列名
     *
     * @param rs
     * @return
     * @throws Exception
     */
    public  List<String> getSqlColumns(ResultSet rs) throws Exception {
        List<String> sqlColumns = new ArrayList<String>();
        try {
            ResultSetMetaData rsmd = rs.getMetaData();
            for (int i = 0; i < rsmd.getColumnCount(); i++) {
                String columnName = rsmd.getColumnName(i + 1);
                sqlColumns.add(columnName);
            }
        } catch (Exception e) {
            LOG.error(e.getMessage());
            throw e;
        }
        return sqlColumns;
    }

    // 将字Clob转成String类型
    public  String clobToString(Clob c) throws SQLException, IOException {
        String reString = "";
        Reader is = c.getCharacterStream();// 得到流
        BufferedReader br = new BufferedReader(is);
        String s = br.readLine();
        StringBuffer sb = new StringBuffer();
        while (s != null) {// 执行循环将字符串全部取出付值给StringBuffer由StringBuffer转成STRING
            sb.append(s);
            s = br.readLine();
        }
        reString = sb.toString();
        return reString;
    }

    /**
     * list集合转换成json
     *
     * @param list
     * @return json字符串
     */
    public  String listTojson(List<Map<String, Object>> list) {
        StringBuilder json = new StringBuilder();
        json.append("[");
        if (list != null && list.size() > 0) {
            for (Map<String, Object> map : list) {
                json.append(new JSONObject(map));
                json.append(",");
            }
            json.setCharAt(json.length() - 1, ']');
        } else {
            json.append("]");
        }
        return json.toString();
    }

    /**
     * 检查能否连接数据库
     */
    public  boolean checkConnectionDB(String driver, String url, String username, String password) {
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            LOG.info("not found driver class:" + driver, e);
            return false;
        }
        Connection con = null;
        try {
            con = DriverManager.getConnection(url, username, password);
            return true;
        } catch (SQLException e) {
            LOG.info("connection database failed, url=" + url, e);
            return false;
        } finally {
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    /**
     * 判断sqlserver表是否存在
     *
     * @param tableName
     * @return
     * @throws Exception
     */
    public  boolean sqlServerTableExist(String tableName) throws Exception {
        boolean b = false;
        String sql = "select * from sys.tables where name=? and type = 'u'";
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            ps.setString(1, tableName);
            rs = ps.executeQuery();
            b = rs.next();
        } catch (Exception e) {
            LOG.error(e.getMessage());
            throw e;
        } finally {
            close();
        }
        return b;
    }

    public  void close() {
        // 关闭资源(先开后关)

        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                LOG.error(e.getMessage());
            }
            rs = null;
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                LOG.error(e.getMessage());
            }
            ps = null;
        }
        if (statement != null) {
            try {
                statement.close();
            } catch (SQLException e) {
                LOG.error(e.getMessage());
            }
            statement = null;
        }
        if (null != conn) {
            try {
                conn.close();
            } catch (SQLException e) {
                LOG.error(e.getMessage());
            }
            conn = null;
        }
    }

    public ResultSet getResultSetInfo(String sql) throws Exception {
        ResultSet rs = null;
        try {
            conn = getConnection();
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            return rs;
        } catch (Exception e) {
            //e.printStackTrace();
            LOG.error(e.getMessage());
            throw e;
        } finally {
            //conn.close();
        }
    }

    /**
     * 校验数据库是否存在
     *
     * @param commitFlag
     * @return
     */
    public  boolean checkDataBaseExist(boolean commitFlag) {
        boolean b = false;
        String sql = "show databases";
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            b = rs.next();
        } catch (Exception e) {
            LOG.error(e.getMessage());
        } finally {
            close();
        }
        return b;
    }
}
